/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yy.bugzillahelp.action.server;

import com.yy.bugzillahelp.data.server.ServerConfigData;
import com.yy.bugzillahelp.data.server.ServerData;
import com.yy.bugzillahelp.db.BugzillaHelpDB;
import com.yy.bugzillahelp.tool.DataTool;
import com.yy.bugzillahelp.tool.SQLTool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author hualun-alan
 */
public class ServerAction {

    public static int insertOne(Connection con, ServerData serverData) {
        String sql1 = "insert into server_info (id,server_name,server_url,user_name,password)"
                + " values (?,?,?,?,?)";

        PreparedStatement ps1 = null;
        try {
            con.setAutoCommit(false);

            ps1 = con.prepareStatement(sql1);
            ps1.setString(1, serverData.getId());
            ps1.setString(2, serverData.getConnectName());
            ps1.setString(3, serverData.getServerUrl());
            ps1.setString(4, serverData.getUserName());
            ps1.setString(5, serverData.getPassword());
            ps1.executeUpdate();

            ServerConfigAction.updateFlushConfig(con, new ServerConfigData(serverData.getId(), true, 1000 * 60 * 15));

            con.commit();
            return 1;
        } catch (SQLException ex) {
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, "sql1 = {0}", sql1);
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps1);
        }
    }

    public static int updateOne(Connection con, ServerData serverData) {
        ServerData oldServerData = searchById(con, serverData.getId());
        if (oldServerData == null) {
            throw new RuntimeException("数据库执行错误！");
        } else if (DataTool.isEmpty(oldServerData.getId())) {
            return insertOne(con, serverData);
        } else {
            String sql1 = "update server_info set server_name=?,server_url=?,user_name=?,password=? where id=?";

            PreparedStatement ps1 = null;
            try {
                con.setAutoCommit(false);

                ps1 = con.prepareStatement(sql1);
                ps1.setString(1, serverData.getConnectName());
                ps1.setString(2, serverData.getServerUrl());
                ps1.setString(3, serverData.getUserName());
                ps1.setString(4, serverData.getPassword());
                ps1.setString(5, serverData.getId());
                ps1.executeUpdate();

                con.commit();
                return 1;
            } catch (SQLException ex) {
                Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, "sql1 = {0}", sql1);
                Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, null, ex);
                return -1;
            } finally {
                SQLTool.closeStatement(ps1);
            }
        }
    }

    public static ServerData searchById(Connection con, String id) {
        ServerData data = new ServerData();

        String sql = "select * from server_info where id=?";
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                data.setId(rs.getString("id"));
                data.setConnectName(rs.getString("server_name"));
                data.setServerUrl(rs.getString("server_url"));
                data.setUserName(rs.getString("user_name"));
                data.setPassword(rs.getString("password"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, "sql = {0}", sql);
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            SQLTool.closeStatement(ps);
        }

        return data;
    }

    /**
     *
     * @param con
     * @param id
     * @return a list of ServerData, null if occurs exception
     */
    public static ArrayList<ServerData> searchAll(Connection con) {
        ArrayList<ServerData> list = new ArrayList<ServerData>();

        String sql = "select * from server_info";
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ServerData data = new ServerData();
                data.setId(rs.getString("id"));
                data.setConnectName(rs.getString("server_name"));
                data.setServerUrl(rs.getString("server_url"));
                data.setUserName(rs.getString("user_name"));
                data.setPassword(rs.getString("password"));
                list.add(data);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, "sql = {0}", sql);
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            SQLTool.closeStatement(ps);
        }

        return list;
    }

    public static int deleteOne(Connection con, String id) {
        String sql = "delete from server_info where id = ?";
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            return ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, "sql = {0}", sql);
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps);
        }
    }

    public static int clearTable(Connection con) {
        String sql = "delete from server_info";
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            return ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, "sql = {0}", sql);
            Logger.getLogger(ServerAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps);
        }
    }

    public static void main(String[] args) {
        Connection con = BugzillaHelpDB.getConnection();
        //        System.out.println("clearTable=" + ServerConfigAction.clearTable(con));
        //        System.out.println("insertFlushConfig=" + insertFlushConfig(con, new ServerData("1", "a", "url1", "u1", "p1")));
        //        System.out.println("searchByServerId=" + searchById(con, "1"));
        //        System.out.println("updateFlushConfig=" + updateFlushConfig(con, new ServerData("1", "a", "url2", "u2", "p2")));
        //        System.out.println("searchByServerId=" + searchById(con, "1"));
        //        System.out.println("deleteOne=" + deleteOne(con, "1"));
        //        System.out.println("searchByServerId=" + searchById(con, "1"));

        ArrayList<ServerData> searchAll = searchAll(con);
        for (ServerData serverData : searchAll) {
            System.out.println(serverData);
        }
        SQLTool.closeConnection(con);
    }
}
